package com.ansion.basedb.dao.helper;

import android.database.Cursor;
import android.text.TextUtils;
import android.util.Log;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

import com.ansion.basedb.database.sql.ColumnType;
import com.ansion.basetool.preconditions.Preconditions;

import net.zetetic.database.sqlcipher.SQLiteDatabase;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;


/**
 * created by zhaoyuntao
 * on 15/03/2022
 * description:
 */
public class SQLExecuteHelper {

    private static final String TAG = "abcdefghijk";

    @ColumnType
    public static int getType(@NonNull String typeString) {
        if (TextUtils.isEmpty(typeString)) {
            return ColumnType.TYPE_TEXT;
        }
        typeString = typeString.toUpperCase(Locale.US);
        if (typeString.startsWith("CHARACTER") ||
                typeString.startsWith("VARCHAR") ||
                typeString.startsWith("VARYING") ||
                typeString.startsWith("NCHAR") ||
                typeString.startsWith("NATIVE CHARACTER") ||
                typeString.startsWith("NVARCHAR")) {
            return ColumnType.TYPE_TEXT;
        }
        switch (typeString) {
            case "INT":
            case "INTEGER":
            case "TINYINT":
            case "SMALLINT":
            case "MEDIUMINT":
            case "BIGINT":
            case "UNSIGNED BIG INT":
            case "INT2":
            case "INT8":
                return ColumnType.TYPE_INTEGER;
            case "BLOB":
                return ColumnType.TYPE_BLOB;
            case "REAL":
            case "DOUBLE":
            case "DOUBLE PRECISION":
            case "FLOAT":
                return ColumnType.TYPE_REAL;
            case "TEXT":
            case "CLOB":
                return ColumnType.TYPE_TEXT;
            case "NULL":
            default:
                return ColumnType.TYPE_NULL;

        }
    }

    public static String getType(@ColumnType int type) {
        switch (type) {
            case ColumnType.TYPE_BLOB:
                return "BLOB";
            case ColumnType.TYPE_INTEGER:
                return "INTEGER";
            case ColumnType.TYPE_REAL:
                return "DOUBLE";
        }
        return "TEXT";
    }

    @Nullable
    public static Object getColumnValue(Cursor cursor, ColumnNameBean columnBean) {
        String columnName = columnBean.name;
        @ColumnType int type = columnBean.type;
        int index = cursor.getColumnIndex(columnName);
        if (index >= 0) {
            switch (type) {
                case ColumnType.TYPE_BLOB:
                    return cursor.getBlob(index);
                case ColumnType.TYPE_INTEGER:
                    return cursor.getLong(index);
                case ColumnType.TYPE_TEXT:
                    return cursor.getString(index);
                case ColumnType.TYPE_REAL:
                    return cursor.getDouble(index);
                case ColumnType.TYPE_NULL:
            }
        }
        return null;
    }

    @NonNull
    public static List<String> getTablesInDb(SQLiteDatabase database) {
//        S.lllls("getTablesInDb start");
        List<String> tableNames = new ArrayList<>();
        if (database != null) {
            Cursor cursor = database.rawQuery("select * from sqlite_master where type = ?", new String[]{"table"});
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    String tableName = cursor.getString(cursor.getColumnIndexOrThrow("tbl_name"));
                    tableNames.add(tableName);
//                    Log.i("tableName:" + tableName);
                } while (cursor.moveToNext());
                cursor.close();
            }
        }
//        S.lllls("getTablesInDb end");
        return tableNames;
    }

    @NonNull
    public static TableBean loadTableColumns(String tableName, SQLiteDatabase database) {
        i("--------------------------------------------------------------------------------------------------------------------------");
        TableBean tableBean = new TableBean();
        tableBean.tableName = tableName;
        if (database != null) {
            Cursor cursor = database.rawQuery("pragma table_info(" + tableName + ")", null);
            if (cursor != null && cursor.moveToFirst()) {

                tableBean.columns = new ArrayList<>(cursor.getColumnCount());
                tableBean.itemBeans = new ArrayList<>(cursor.getCount());

                do {
                    String columnName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                    String typeString = cursor.getString(cursor.getColumnIndexOrThrow("type"));
                    int primaryKey = cursor.getInt(cursor.getColumnIndexOrThrow("pk"));
                    int notNull = cursor.getInt(cursor.getColumnIndexOrThrow("notnull"));
                    ColumnNameBean columnBean = new ColumnNameBean();
                    columnBean.type = getType(typeString);
                    columnBean.typeString = typeString;
                    columnBean.name = columnName;
                    columnBean.isPrimaryKey = primaryKey == 1;
                    columnBean.notNull = notNull == 1;
                    tableBean.columns.add(columnBean);
                } while (cursor.moveToNext());
                cursor.close();
                i("Table name: [" + tableName + "] columns: " + tableBean.getColumnInfoString(false));
            }
        }
        i("--------------------------------------------------------------------------------------------------------------------------");
        return tableBean;
    }

    public static void _execRawSQL(@NonNull String sql, SQLiteDatabase database) {
        Preconditions.checkNotEmpty(sql);
        i("execute raw SQL: [" + sql + "]");
        try {
            database.rawExecSQL(sql);
            i("execSQL sql: [ " + sql + " ] finish");
        } catch (Throwable e) {
            e("execSQL sql: [ " + sql + " ] error:" + Log.getStackTraceString(e));
        }
        i("--------------------------------------------------------------------------------------------------------------------------");
    }

    public static void loadAllItems(@NonNull String sql, @NonNull TableBean tableBean, boolean formatJson, boolean formatBlob, int rowMaxLength, SQLiteDatabase database) {
        Preconditions.checkNotEmpty(sql);
        Preconditions.checkNotEmpty(tableBean.tableName);
        Cursor cursor = database.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            List<String> columnNames = Arrays.asList(cursor.getColumnNames());
            do {
                ItemBean itemBean = new ItemBean();
                itemBean.columnBeanMap = new HashMap<>(tableBean.columns.size());
                tableBean.itemBeans.add(itemBean);

                a:
                for (int i = 0; i < columnNames.size(); i++) {
                    String columnName = columnNames.get(i);
                    b:
                    for (ColumnNameBean columnNameBean : tableBean.columns) {
                        if (columnName.equalsIgnoreCase(columnNameBean.name)) {
                            try {
                                Object value = getColumnValue(cursor, columnNameBean);
                                if (value == null) {
                                    value = TableBean.NULL;
                                }
                                ColumnBean columnBean = new ColumnBean(columnNameBean, value);
                                itemBean.columnBeanMap.put(columnBean.name, columnBean);
                            } catch (Throwable e) {
                                e(tableBean.error = Log.getStackTraceString(e));
                            }
                            continue a;
                        }
                    }
                    ColumnNameBean columnNameBean = new ColumnNameBean();
                    columnNameBean.name = columnName;
                    int index = cursor.getColumnIndex(columnName);
                    if (index >= 0) {
                        String value = cursor.getString(index);
                        ColumnBean columnBean = new ColumnBean(columnNameBean, value == null ? TableBean.NULL : value);
                        itemBean.columnBeanMap.put(columnName, columnBean);
                    }
                }
            } while (cursor.moveToNext());
            List<String> result = tableBean.getRowInfoString(formatJson, formatBlob, rowMaxLength);
            i("Table: [" + tableBean.tableName + "] SQL: [" + sql + "]  row size: " + result.size());
            for (String row : result) {
                i(row);
            }
            cursor.close();
        } else {
            e("cursor is null or empty");
            tableBean.error = "SQL executed success with empty result.";
        }
        i("--------------------------------------------------------------------------------------------------------------------------");
    }

    @Nullable
    public static TableBean executeSql(String sql, SQLiteDatabase database, boolean formatJson, boolean formatBlob, int rowMaxLength) {
        if (sql.toLowerCase(Locale.ROOT).trim().startsWith("select")) {
            List<String> tableNames = getTablesInDb(database);
            for (String tableName : tableNames) {
                if (TextUtils.isEmpty(tableName)) {
                    continue;
                }
                if (sql.contains(tableName)) {
                    TableBean tableBean = loadTableColumns(tableName, database);
                    try {
                        loadAllItems(sql, tableBean, formatJson, formatBlob, rowMaxLength, database);
                    } catch (Throwable e) {
                        e(tableBean.error = Log.getStackTraceString(e));
                    }
                    return tableBean;
                }
            }
            e("sql not contains any tableName: [" + sql + "]");
        } else {
            _execRawSQL(sql, database);
        }
        return null;
    }

    private static void i(String msg) {
        Log.i(TAG, TAG + msg);
    }

    private static void e(String msg) {
        Log.e(TAG, TAG + msg);
    }
}
